package com.shebao.utils.excel;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.jeecgframework.poi.excel.ExcelImportUtil;
import org.jeecgframework.poi.excel.entity.ImportParams;

public class Util {
	public void tt(){
		ImportParams params = new ImportParams();
		List<PersonEntity> list = ExcelImportUtil.importExcel(
		           new File("D://1.xls"),
		           PersonEntity.class, params);
		List<String> sqlList=new ArrayList<>(3000);
		int id=1201;
		for(int i=0;i<list.size();i++){
			PersonEntity pe=list.get(i);
			StringBuilder sb=new StringBuilder("INSERT INTO shebao_person VALUES (");
			sb.append(id).append(",'");
			sb.append(pe.getUserName()).append("','");
			sb.append(pe.getSocialId()).append("','");
			sb.append(pe.getCardId()).append("','");
			sb.append(pe.getCode()).append("','");
			sb.append(pe.getPayDate().split("\\.")[0]).append("','");
			sb.append(pe.getCompanyId().split("\\.")[0]).append("','");
			
			sb.append(pe.getOldBase()).append("','");
			sb.append(pe.getOldCompany()).append("','");
			sb.append(pe.getOldPerson()).append("','");
			
			sb.append(pe.getMedicalBase()).append("','");
			sb.append(pe.getMedicalCompany()).append("','");
			sb.append(pe.getMedicalPerson()).append("','");
			
			sb.append(pe.getSupplyBase()).append("','");
			sb.append(pe.getSupplyCompany()).append("','");
			sb.append(pe.getSupplyPerson()).append("','");
			sb.append(pe.getBirthBase()).append("','");
			sb.append(pe.getBirthCompany()).append("','");
			sb.append(pe.getBirthPerson()).append("','");
			
			sb.append(pe.getUnemploymentBase()).append("','");
			sb.append(pe.getUnemploymentCompany()).append("','");
			sb.append(pe.getUnemploymentPerson()).append("','");
			
			sb.append(pe.getHurtBase()).append("','");
			sb.append(pe.getHurtCompany()).append("'");
			sb.append(");");
			sqlList.add(sb.toString());
			id++;
			System.out.println(sb.toString());
		}
		write(sqlList);
	}
	private void write(List<String> sqlList){
		File writename = new File("D://output.txt"); 
		try {
			writename.createNewFile();
			BufferedWriter out = new BufferedWriter(new FileWriter(writename,true)); 
			for(String s:sqlList){
				out.write(s+"\r\n"); // \r\n即为换行  
			}
			
			out.flush(); // 把缓存区内容压入文件  
            out.close(); // 最后记得关闭文件
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} // 创建新文件
		
	}
	public static void main(String[] args) {
		new Util().tt();
	}
}
